<?php
class Post
{
    private $db;

    public function __construct($db)
    {
        $this->db = $db;
    }

    // 获取最热门的攻略帖
    public function getTopViewedPosts($limit = 5)
    {
        // 确保limit是正整数
        $limit = max(1, (int) $limit);
        $limit = min($limit, 100); // 设置上限防止过大查询

        // 修改SQL语句，对于LIMIT参数使用正确的占位符
        $sql = "
            SELECT
                p.*,
                u.username,
                u.avatar_url
            FROM
                posts p
            JOIN
                users u ON p.user_id = u.id
            ORDER BY
                p.view_count DESC
            LIMIT :limit
        ";


        $stmt = $this->db->prepare($sql);
        $stmt->bindValue(':limit', $limit, PDO::PARAM_INT);
        $stmt->execute();

        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    //  获取指定分类下的帖子
    public function getPostsByCategory($categoryId)
    {
        try {
            $stmt = $this->db->prepare("
                SELECT
                    p.*,
                    u.username,
                    u.avatar_url
                FROM
                    posts p
                JOIN
                    users u ON p.user_id = u.id
                WHERE
                    p.category_id = :category_id
                ORDER BY
                    p.created_at DESC
            ");

            $stmt->bindValue(':category_id', $categoryId, PDO::PARAM_INT);
            $stmt->execute();

            return $stmt->fetchAll(PDO::FETCH_ASSOC);

        } catch (PDOException $e) {
            error_log("Database error in Post model: " . $e->getMessage());
            throw new Exception("Failed to retrieve posts by category");
        }
    }

    // 搜索帖子
    public function searchPosts($keyword, $orderBy = 'view_count', $orderDirection = 'DESC')
    {
        try {
            // 验证排序字段和方向以防止SQL注入
            $allowedOrderFields = ['view_count', 'created_at', 'comment_count'];
            $orderBy = in_array($orderBy, $allowedOrderFields) ? $orderBy : 'view_count';
            $orderDirection = strtoupper($orderDirection) === 'ASC' ? 'ASC' : 'DESC';

            $stmt = $this->db->prepare("
                SELECT
                    p.*,
                    u.username,
                    u.avatar_url,
                    (SELECT COUNT(*) FROM comments c WHERE c.post_id = p.id) AS comment_count
                FROM
                    posts p
                JOIN
                    users u ON p.user_id = u.id
                WHERE
                    p.title LIKE :keyword
                    OR p.content LIKE :keyword
                ORDER BY
                    p.{$orderBy} {$orderDirection}
            ");

            $searchKeyword = '%' . $keyword . '%';
            $stmt->bindValue(':keyword', $searchKeyword, PDO::PARAM_STR);
            $stmt->execute();

            return $stmt->fetchAll(PDO::FETCH_ASSOC);

        } catch (PDOException $e) {
            error_log("Database error in Post model: " . $e->getMessage());
            throw new Exception("Failed to search posts");
        }
    }

    // 获取指定id下的帖子
    public function getPostById($postId)
    {
        try {
            $stmt = $this->db->prepare("
                SELECT
                    p.*,
                    u.username,
                    u.avatar_url
                FROM
                    posts p
                JOIN
                    users u ON p.user_id = u.id
                WHERE
                    p.id = :post_id
                LIMIT 1
            ");

            $stmt->bindValue(':post_id', $postId, PDO::PARAM_INT);
            $stmt->execute();

            $post = $stmt->fetch(PDO::FETCH_ASSOC);

            if (!$post) {
                throw new Exception("Post not found");
            }

            return $post;

        } catch (PDOException $e) {
            error_log("Database error in Post model: " . $e->getMessage());
            throw new Exception("Failed to retrieve post");
        }
    }

    //  获取指定id下贴子评论
    public function getCommentsByPostId($postId)
    {
        $query = "SELECT c.*, u.username, u.avatar_url
                  FROM comments c
                  JOIN users u ON c.user_id = u.id
                  WHERE c.post_id = :post_id
                  ORDER BY c.created_at DESC";

        $stmt = $this->db->prepare($query);
        $stmt->bindParam(':post_id', $postId, PDO::PARAM_INT);
        $stmt->execute();

        $comments = $stmt->fetchAll(PDO::FETCH_ASSOC);

        if (empty($comments)) {
            return []; // 返回空数组而不是抛出异常
        }

        return $comments;
    }

    // 获取评论
    public function insertComment($postId, $userId, $content)
    {
        $query = "INSERT INTO comments (post_id, user_id, content)
                  VALUES (:post_id, :user_id, :content)";

        $stmt = $this->db->prepare($query);
        $stmt->bindParam(':post_id', $postId, PDO::PARAM_INT);
        $stmt->bindParam(':user_id', $userId, PDO::PARAM_INT);
        $stmt->bindParam(':content', $content, PDO::PARAM_STR);
        $stmt->execute();

        return $this->db->lastInsertId();
    }

    // 获取新插入数据
    public function getCommentById($commentId)
    {
        $query = "SELECT c.*, u.username, u.avatar_url
                  FROM comments c
                  JOIN users u ON c.user_id = u.id
                  WHERE c.id = :comment_id";

        $stmt = $this->db->prepare($query);
        $stmt->bindParam(':comment_id', $commentId, PDO::PARAM_INT);
        $stmt->execute();

        $comment = $stmt->fetch(PDO::FETCH_ASSOC);

        if (!$comment) {
            throw new Exception('Comment not found');
        }

        return $comment;
    }

    // 发帖
    public function insertPost($userId, $categoryId, $title, $content, $status = '正常')
    {
        $query = "INSERT INTO posts
                  (user_id, category_id, title, content, status)
                  VALUES
                  (:user_id, :category_id, :title, :content, :status)";

        $stmt = $this->db->prepare($query);
        $stmt->bindParam(':user_id', $userId, PDO::PARAM_INT);
        $stmt->bindParam(':category_id', $categoryId, PDO::PARAM_INT);
        $stmt->bindParam(':title', $title, PDO::PARAM_STR);
        $stmt->bindParam(':content', $content, PDO::PARAM_STR);
        $stmt->bindParam(':status', $status, PDO::PARAM_STR);
        $stmt->execute();

        return $this->db->lastInsertId();
    }

    // 根据指定帖子
    public function getNewPostById($postId)
    {
        $query = "SELECT * FROM posts WHERE id = :post_id";

        $stmt = $this->db->prepare($query);
        $stmt->bindParam(':post_id', $postId, PDO::PARAM_INT);
        $stmt->execute();

        return $stmt->fetch(PDO::FETCH_ASSOC);
    }
}
?>